
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 10/24/2011 16:28:28
-- Generated from EDMX file: F:\MyPrograms\CommonProjects\SalaryQuery\Model1.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [Ession];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_StaticsEmployee]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[StaticsSet] DROP CONSTRAINT [FK_StaticsEmployee];
GO
IF OBJECT_ID(N'[dbo].[FK_SalaryMonthEEmployee]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[SalaryMonthESet] DROP CONSTRAINT [FK_SalaryMonthEEmployee];
GO
IF OBJECT_ID(N'[dbo].[FK_OtherIncomeEmployee]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[OtherIncomeSet] DROP CONSTRAINT [FK_OtherIncomeEmployee];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[SalaryMonthESet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[SalaryMonthESet];
GO
IF OBJECT_ID(N'[dbo].[OtherIncomeSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[OtherIncomeSet];
GO
IF OBJECT_ID(N'[dbo].[StaticsSet]', 'U') IS NOT NULL
    DROP TABLE [dbo].[StaticsSet];
GO
IF OBJECT_ID(N'[dbo].[Departments]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Departments];
GO
IF OBJECT_ID(N'[dbo].[Employees]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Employees];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'SalaryMonthESet'
CREATE TABLE [dbo].[SalaryMonthESet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [月份] nvarchar(max)  NOT NULL,
    [姓名] nvarchar(max)  NOT NULL,
    [实发小计] nvarchar(max)  NOT NULL,
    [岗位工资级别] nvarchar(max)  NOT NULL,
    [岗位工资] nvarchar(max)  NOT NULL,
    [物业补贴] nvarchar(max)  NOT NULL,
    [住房补贴] nvarchar(max)  NOT NULL,
    [加班费] nvarchar(max)  NOT NULL,
    [公积金扣款] nvarchar(max)  NOT NULL,
    [社会保险扣款] nvarchar(max)  NOT NULL,
    [备注] nvarchar(max)  NOT NULL,
    [年份] nvarchar(max)  NOT NULL,
    [EmployeeC_P_CODE_] varchar(50)  NOT NULL
);
GO

-- Creating table 'OtherIncomeSet'
CREATE TABLE [dbo].[OtherIncomeSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [发放日期] nvarchar(max)  NOT NULL,
    [类型] nvarchar(max)  NOT NULL,
    [实发] nvarchar(max)  NOT NULL,
    [应发] nvarchar(max)  NOT NULL,
    [税] nvarchar(max)  NOT NULL,
    [备注] nvarchar(max)  NOT NULL,
    [EmployeeC_P_CODE_] varchar(50)  NOT NULL
);
GO

-- Creating table 'StaticsSet'
CREATE TABLE [dbo].[StaticsSet] (
    [Id] int IDENTITY(1,1) NOT NULL,
    [姓名] nvarchar(max)  NOT NULL,
    [年份] nvarchar(max)  NOT NULL,
    [统计类型] nvarchar(max)  NOT NULL,
    [实发收入合计] nvarchar(max)  NOT NULL,
    [应发收入合计] nvarchar(max)  NOT NULL,
    [应发工资] nvarchar(max)  NOT NULL,
    [绩效奖金] nvarchar(max)  NOT NULL,
    [个税合计] nvarchar(max)  NOT NULL,
    [EmployeeC_P_CODE_] varchar(50)  NOT NULL
);
GO

-- Creating table 'Departments'
CREATE TABLE [dbo].[Departments] (
    [ORGAN_ID] int  NOT NULL,
    [ORGAN_CODE] varchar(50)  NULL,
    [ORGAN_NAME] varchar(50)  NULL,
    [LAYER_CODE] varchar(50)  NULL,
    [SUPERIOR_ORGAN] varchar(50)  NULL,
    [MANAGE_ORGAN] varchar(50)  NULL,
    [NAME_JP] varchar(50)  NULL,
    [SORT_NO] varchar(50)  NULL,
    [STATUS] varchar(50)  NULL,
    [CHANGED_DATE] varchar(50)  NULL,
    [VIRTUAL_FLAG] varchar(50)  NULL,
    [FULL_PATH] varchar(150)  NULL
);
GO

-- Creating table 'Employees'
CREATE TABLE [dbo].[Employees] (
    [C_P_CODE_] varchar(50)  NOT NULL,
    [C_NAME_] varchar(50)  NULL,
    [C_SURNAME_EN_] varchar(50)  NULL,
    [C_FIRSTNAME_EN_] varchar(50)  NULL,
    [C_SEX_] varchar(50)  NULL,
    [C_BIRTHDAY_] varchar(50)  NULL,
    [C_ORGAN_ID_] varchar(50)  NULL,
    [C_TEL_HOME_] varchar(50)  NULL,
    [C_TEL_OFFICE_] varchar(50)  NULL,
    [C_MOBILE_] varchar(50)  NULL,
    [C_E_MAIL_] varchar(50)  NULL,
    [C_FAX_] varchar(50)  NULL,
    [C_PASSWORD_] varchar(50)  NULL,
    [C_SERVER_NAME_] varchar(50)  NULL,
    [C_MAIL_PATH_] varchar(50)  NULL,
    [C_NAME_JP_] varchar(50)  NULL,
    [C_NAME_QP_] varchar(50)  NULL,
    [C_SORT_NO_] varchar(50)  NULL,
    [C_STATUS_] varchar(50)  NULL,
    [C_CHANGED_DATE_] varchar(50)  NULL,
    [C_ROLE_INFO_] varchar(50)  NULL,
    [C_POST_ALL_] varchar(50)  NULL,
    [C_LDAP_FLAG_] varchar(50)  NULL,
    [C_DUTY_LEVEL_] varchar(50)  NULL,
    [C_VIRTUAL_FLAG_] varchar(50)  NULL,
    [DepartmentORGAN_ID] int  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'SalaryMonthESet'
ALTER TABLE [dbo].[SalaryMonthESet]
ADD CONSTRAINT [PK_SalaryMonthESet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'OtherIncomeSet'
ALTER TABLE [dbo].[OtherIncomeSet]
ADD CONSTRAINT [PK_OtherIncomeSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'StaticsSet'
ALTER TABLE [dbo].[StaticsSet]
ADD CONSTRAINT [PK_StaticsSet]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [ORGAN_ID] in table 'Departments'
ALTER TABLE [dbo].[Departments]
ADD CONSTRAINT [PK_Departments]
    PRIMARY KEY CLUSTERED ([ORGAN_ID] ASC);
GO

-- Creating primary key on [C_P_CODE_] in table 'Employees'
ALTER TABLE [dbo].[Employees]
ADD CONSTRAINT [PK_Employees]
    PRIMARY KEY CLUSTERED ([C_P_CODE_] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [EmployeeC_P_CODE_] in table 'StaticsSet'
ALTER TABLE [dbo].[StaticsSet]
ADD CONSTRAINT [FK_StaticsEmployee]
    FOREIGN KEY ([EmployeeC_P_CODE_])
    REFERENCES [dbo].[Employees]
        ([C_P_CODE_])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_StaticsEmployee'
CREATE INDEX [IX_FK_StaticsEmployee]
ON [dbo].[StaticsSet]
    ([EmployeeC_P_CODE_]);
GO

-- Creating foreign key on [EmployeeC_P_CODE_] in table 'SalaryMonthESet'
ALTER TABLE [dbo].[SalaryMonthESet]
ADD CONSTRAINT [FK_SalaryMonthEEmployee]
    FOREIGN KEY ([EmployeeC_P_CODE_])
    REFERENCES [dbo].[Employees]
        ([C_P_CODE_])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SalaryMonthEEmployee'
CREATE INDEX [IX_FK_SalaryMonthEEmployee]
ON [dbo].[SalaryMonthESet]
    ([EmployeeC_P_CODE_]);
GO

-- Creating foreign key on [EmployeeC_P_CODE_] in table 'OtherIncomeSet'
ALTER TABLE [dbo].[OtherIncomeSet]
ADD CONSTRAINT [FK_OtherIncomeEmployee]
    FOREIGN KEY ([EmployeeC_P_CODE_])
    REFERENCES [dbo].[Employees]
        ([C_P_CODE_])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_OtherIncomeEmployee'
CREATE INDEX [IX_FK_OtherIncomeEmployee]
ON [dbo].[OtherIncomeSet]
    ([EmployeeC_P_CODE_]);
GO

-- Creating foreign key on [DepartmentORGAN_ID] in table 'Employees'
ALTER TABLE [dbo].[Employees]
ADD CONSTRAINT [FK_DepartmentEmployee]
    FOREIGN KEY ([DepartmentORGAN_ID])
    REFERENCES [dbo].[Departments]
        ([ORGAN_ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_DepartmentEmployee'
CREATE INDEX [IX_FK_DepartmentEmployee]
ON [dbo].[Employees]
    ([DepartmentORGAN_ID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------